﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace QLBH.DataAccess
{
    class clsDAL 
    {
        private SqlConnection _Connection;
        private SqlCommand _Command;
        private static string _ConnectionString;
        private bool _AutoCommit = true;

        public bool AutoCommit
        {
            get { return _AutoCommit; }
            set { _AutoCommit = value; }
        }

        public clsDAL()
        {
            if (_ConnectionString == null || _ConnectionString == string.Empty)
            {
                _ConnectionString = fcnCreateConnectionString();
            }
        }

        #region "Connection String"

        private string fcnCreateConnectionString()
        {
            string cnStr = "";
            cnStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            return cnStr;
        }

        #endregion

        #region "Process Connection"

        public void Connect()
        {
            _Connection = new SqlConnection();
            _Command = new SqlCommand();
            _Connection.ConnectionString = _ConnectionString;

            try
            {
                if (_Connection.State == ConnectionState.Closed || _Connection.State == ConnectionState.Broken)
                {
                    _Connection.Open();
                    _Command.Connection = _Connection;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Disconnect()
        {
            try
            {
                if (_Connection == null)
                {
                    return;
                }
                else if (_Connection.State != ConnectionState.Closed && _Connection.State != ConnectionState.Broken)
                {
                    _Connection.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region "Process Transaction"

        public void BeginTransaction()
        {
            try
            {
                _Command.Transaction = _Connection.BeginTransaction();
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public void CommitTransaction()
        {
            try
            {
                _Command.Transaction.Commit();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void RollbackTransaction()
        {
            try
            {
                _Command.Transaction.Rollback();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region "Execute SQL"

        public bool IsExist(string table_name, string where_clause)
        {
            where_clause = where_clause + "";
            string sql = "select count(*) from " + table_name;
            if (where_clause != "")
            {
                sql += " where " + where_clause;
            }

                return GetRowCount(sql) > 0;
            
        }

        public int GetRowCount(string sql)
        {
            int rowCount = 0;
            DataTable dt = ExecuteQuery(sql);

            if (dt != null)
            {
                if (dt.Rows.Count != 0)
                {
                    return Int32.Parse(dt.Rows[0][0].ToString());
                }
            }
            return rowCount;
        }

        public bool ExecuteNonQuery(string sql)
        {
            bool result = false;
            int iRet = -1;
            try
            {
                Connect();
            }
            catch (Exception ex)
            {
                Disconnect();
                return result;
            }

            try
            {
                BeginTransaction();
                _Command.CommandText = sql;
                iRet = _Command.ExecuteNonQuery();
                if (AutoCommit)
                {
                    CommitTransaction();
                    Disconnect();
                }
                result = iRet > 0;
            }
            catch (Exception ex)
            {
                RollbackTransaction();
                Disconnect();
            }
            return result;
        }

        public bool Delete(string table_name, string where_clause)
        {
            bool result = false;
            string sql = "";
            sql = "delete from " + table_name + " where " + where_clause;
            result = ExecuteNonQuery(sql);
            return result;
        }

        #endregion

        #region "Fill table"

        public DataTable ExecuteQuery(string sql)
        {

            DataTable oDt = new DataTable();
            try
            {
                Connect();
                SqlDataAdapter sqlDA = new SqlDataAdapter(sql, _Connection);
                sqlDA.Fill(oDt);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
            finally
            {
                Disconnect();
            }
            return oDt;
        }

        public DataRow getRow(string sql)
        {
            DataRow dr = null;

            DataTable dt = ExecuteQuery(sql);
            if (dt.Rows.Count > 0)
            {
                dr = dt.Rows[0];
            }

            return dr;
        }

        #endregion
    }
}
